import random
import string
from datetime import datetime, timedelta

import pyodbc
from flask import Flask, request, jsonify, redirect, url_for
from flask_cors import CORS

import config

app = Flask(__name__)
CORS(app)  # 跨域

# 数据库连接信息
server = config.server
database = config.database
username = config.username
password = config.password
driver = config.driver
cnxn = pyodbc.connect(f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}')


# 前端页面渲染
@app.route('/')
def index():
    return redirect(url_for('static', filename='index.html'))


# 登陆验证
@app.route('/login', methods=['POST'])
def login():
    # 获取用户名和密码
    username = request.form.get('username')
    password = request.form.get('password')
    # 调用存储过程进行登录验证
    cursor = cnxn.cursor()
    cursor.execute("EXEC users_login @p_username=?, @p_password=?", username, password)
    result = cursor.fetchone()
    # 从查询结果中获取 flag 的值
    # flag = 0 if result[0] == 'True' else 1
    # flag = 0 if result[1] == 'False' else 1

    # 根据返回的状态信息进行相应的处理
    if result[0] == 'success':
        return jsonify({'status': 'success', 'flag': result[1]})

    else:
        return jsonify({'status': 'fail', 'message': '用户名或密码错误'})


# 管理员页面
# 添加用户模块
@app.route('/add', methods=['POST'])
def add_user():
    # 获取前端页面传来的用户名和密码
    username = request.form['username']
    password = request.form['password']
    # 查询数据库中是否已经存在相同的用户名
    cursor = cnxn.cursor()
    cursor.execute("SELECT COUNT(*) FROM users WHERE username = ?", (username,))
    count = cursor.fetchone()[0]
    # 如果存在相同的用户名，则返回一个错误消息
    if count > 0:
        return "Username already exists"
    # 如果不存在相同的用户名，则将新用户插入数据库
    else:
        # 查询当前表中最大的ID值
        cursor.execute("SELECT MAX(ID) FROM users")
        max_id = cursor.fetchone()[0]
        # 如果当前表中没有任何记录，则将新记录的ID设置为1
        if max_id is None:
            new_id = 1
        # 如果当前表中有记录，则将新记录的ID设置为最大ID值加1
        else:
            new_id = max_id + 1
        # 将用户信息插入数据库
        on = 'SET IDENTITY_INSERT users ON'
        cursor.execute(on)
        cursor.execute("INSERT INTO users (ID, username, password, flag) VALUES (?, ?, ?, ?)",
                       (new_id, username, password, 1))
        off = 'SET IDENTITY_INSERT users OFF'
        cursor.execute(off)
        cnxn.commit()
        return "User added successfully"


# 删除用户模块
@app.route('/delete', methods=['POST'])
def delete_user():
    # 获取前端页面传来的用户名
    username = request.form['username']
    # 查询数据库中是否存在该用户信息
    cursor = cnxn.cursor()
    cursor.execute("SELECT * FROM users WHERE username=?", (username,))
    user = cursor.fetchone()
    # 如果存在该用户信息，则从数据库中删除用户信息
    if user:
        cursor.execute("DELETE FROM users WHERE username=?", (username,))
        cnxn.commit()
        # 重新设置ID的自动增长值
        cursor.execute("SELECT MAX(ID) FROM users")
        max_id = cursor.fetchone()[0]
        if max_id is not None:
            cursor.execute(f"DBCC CHECKIDENT ('users', RESEED, {max_id})")
        else:
            cursor.execute(f"DBCC CHECKIDENT ('users', RESEED, 0)")
        cnxn.commit()
        return "User deleted successfully"
    # 如果不存在该用户信息，则返回错误提示信息
    else:
        return "User not found"


# 展示所有用户信息模块
@app.route('/users', methods=['GET'])
def show_users():
    # 查询数据库中所有用户信息
    cursor = cnxn.cursor()
    cursor.execute("SELECT * FROM users")
    users = cursor.fetchall()
    # 将所有用户信息以表格的方式展示在前端页面上
    html = "<table><tr><th>ID</th><th>Username</th><th>Password</th><th>Email</th><th>Mobile Phone</th><th>Fullname</th><th>Flag</th></tr>"
    for user in users:
        html += f"<tr><td>{user[0]}</td><td>{user[1]}</td><td>{user[2]}</td><td>{user[3]}</td><td>{user[4]}</td><td>{user[5]}</td><td>{user[6]}</td></tr>"
    html += "</table>"
    return html


# 搜索功能小模块
@app.route('/search/<username>', methods=['GET'])
def search_user(username):
    cur = cnxn.cursor()
    user = cur.execute(f"select * from users where username= ? ", username).fetchone()
    print(user)
    if user is None:
        return jsonify({'error': '未找到该用户'}), 404
    return jsonify({
        'id': user.id,
        'username': user.username,
        'password': user.password,
        'email': user.email,
        'mobile_phone': user.mobile_phone,
        'fullname': user.fullname,
        'flag': user.flag
    }), 200


# 注册车主信息表
@app.route('/api/owner', methods=['POST'])
def add_owner():
    id_code = request.form.get('id_code')
    name = request.form.get('name')
    gender = request.form.get('gender')
    birthdate = request.form.get('birthdate')
    mobile_phone = request.form.get('mobile_phone')
    email = request.form.get('email')
    cursor = cnxn.cursor()
    # 检查身份证号是否已存在
    cursor.execute("SELECT * FROM owner WHERE id_code=?", id_code)
    if cursor.fetchone():
        return jsonify({'error': '该身份证号已存在'}), 400

    # 插入车主信息
    cursor.execute(
        "INSERT INTO owner (id_code, name, gender, birthdate, mobile_phone, email) VALUES (?, ?, ?, ?, ?, ?)",
        id_code, name, gender, birthdate, mobile_phone, email)
    cnxn.commit()

    return jsonify({'message': '车主信息添加成功'}), 201


# 查询车主信息模块
@app.route('/api/owner/<id_code>', methods=['GET'])
def get_owner(id_code):
    email = request.args.get('email', '')
    cursor = cnxn.cursor()
    cursor.execute("SELECT * FROM owner WHERE id_code=? and email=? ", (id_code, email))
    row = cursor.fetchone()
    cursor.execute('select plate_number from license_plate_reg where owner_id= (select id from owner where id_code=? )',
                   id_code)
    plates = [plate[0] for plate in cursor.fetchall()]
    if row:
        owner = {
            'id': row[0],
            'id_code': row[1],
            'name': row[2],
            'gender': row[3],
            'birthdate': row[4].strftime('%Y-%m-%d'),
            'mobile_phone': row[5],
            'email': row[6],
            'plate_number': plates
        }
        return jsonify(owner)
    else:
        return jsonify({'error': '身份证或电子邮件错误！'}), 404


# 车牌方法录入界面  处理表单提交请求
@app.route('/submit', methods=['POST'])
def submit():
    plate_number = request.form['plate_number']  # 车牌号码
    city = request.form['city']  # 城市
    reg_date = datetime.strptime(request.form['reg_date'], '%Y-%m-%d')  # 注册日期
    # 假设到期日期为注册日期的十年后
    expire_date = reg_date + timedelta(days=365 * 10)  # 到期日期
    id_code = request.form['id_code']  # 对应的车主信息 通过身份证查询
    brand = request.form['brand']  # 品牌对应的id
    model = request.form['model']
    color = request.form['color']  # 颜色
    engine_code = request.form['engine_code']  # 车型号
    type = request.form['type']  # 类型
    user_id = request.form['user_id']  # 执行操作的用户的id
    # 在此处进行数据库操作，返回处理结果
    cur = cnxn.cursor()
    # 查询出城市对应的id
    sql = '''select id from cn_city where city_name = ? '''
    cur.execute(sql, city)
    city_id = cur.fetchone()[0]
    # print(city_id)
    # 通过身份证查询到车主表中对应的id
    cur2 = cnxn.cursor()
    sql2 = '''select id from owner where id_code = ?'''
    cur2.execute(sql2, id_code)
    owner_id = cur2.fetchone()[0]
    # print(owner_id)
    cur2.close()

    # 品牌
    cur3 = cnxn.cursor()
    sql3 = '''select id from brand where brand_name=? and Model=? '''
    cur3.execute(sql3, (brand, model))
    brand_id = cur3.fetchone()[0]
    # print(brand_id)
    cur3.close()

    # 执行用户的id
    cur4 = cnxn.cursor()
    sql4 = '''select id from users where username=?'''
    cur4.execute(sql4, user_id)
    userid = cur4.fetchone()[0]
    cur4.close()

    # 组合车牌号码
    cur5 = cnxn.cursor()
    sql6 = '''select plate_head from p_province_city where city_name=?'''
    cur5.execute(sql6, city)
    plate_head = cur5.fetchone()[0]
    plate = plate_head + "•" + plate_number
    # print(plate)
    cur5.execute("SELECT * FROM license_plate_reg WHERE plate_number=?", plate)
    if cur5.fetchone():
        return jsonify({'error': '该车牌号已存在'}), 400
    # print("fail")
    else:
        cur6 = cnxn.cursor()
        cur6.execute('select * from license_plate_reg where engine_code=?', engine_code)
        if cur6.fetchone():
            return jsonify({'error': '该发动机型号已经注册过,请重新输入!'}), 400
        cur5.execute(
            "insert into license_plate_reg(plate_number,city_id,reg_date,expire_date,owner_id,brand_id,color,engine_code,type,user_id) values(?,?,?,?,?,?,?,?,?,?)",
            plate, city_id, reg_date, expire_date, owner_id, brand_id, color, engine_code, type, userid)
        cur5.commit()
        # 更新用户表的plate_id
        # 查询

        # cur5.execute(
        #     f"update owner set plate_id= (select id from license_plate_reg where owner_id=(select id from owner where id = {owner_id})) where id_code={id_code} ")
        # cur5.commit()
        cur5.close()
        cnxn.close()
        result = {'status': 'success', 'message': '信息录入成功！'}
        return jsonify(result)


# 随机生成车牌后五位数字
@app.route('/api/random-plate')
def random_plate():
    plate_number = ''
    for i in range(5):
        if i in [0, 1]:
            plate_number += random.choice([x for x in string.ascii_uppercase if x not in 'OI'])
        else:
            plate_number += random.choice(string.digits)
    plate_number_list = list(plate_number)
    random.shuffle(plate_number_list)
    plate_number = ''.join(plate_number_list)
    return jsonify({'plateNumber': plate_number})

if __name__ == '__main__':
    app.run(debug=True, host='127.1.1.1', port=888)
